﻿#! /usr/bin/perl -w
#######################################################
# Create: P.Linux
# Function: Run SQL On Cluster Server
# Usage: Run on any computer with Perl
# License: GPL v2
# Site: PengLiXun.COM
# Modify: 
# P.Linux 2010-05-13 
#    - Create 1.0 Release
# P.Linux 2010-06-07
#    - Modify 1.1 Release
#    - Fix Some Bugs
# P.Linux 2010-06-21
#    - Modify 1.2 Beta
########################################################
use strict;
use DBI;
use DBD::mysql;
#use DBD::Oracle;
########################################################
# Define Const
# 定义常量
my $c_host = 'Hosts';
my $c_instance = 'Instances';
my $c_schema = 'Schemas';
my $c_cluster = 'Clusters'; 

# Global Var
# 全局变量
my $clear; #清屏命令

# Connect to database via DBI
# 通过DBI连接数据库
my $dbh;
my @dbconn;
my($host, $user, $pwd);
$host = '127.0.0.1';
$user = 'zeus';
$pwd = 'zeus';

# CmdLine Option vars
# 命令行参数变量
my $db_type;

# Version
my $version='1.2 Beta';

#######################################################
# Main Program
# 主程序
#######################################################

# Init
# 初始化程序
&init();

# Main Menu
# 显示主界面
&main_menu();

# Crate MySQL Database Connect
# 创建MySQL数据库连接
&create_conn();

# Run SQL
# 执行SQL
&run();

# Disconnect from MySQL
# 从数据库断开连接
&close_conn();

#######################################################
# Init
# 初始化
#######################################################
sub init {
    $dbh = DBI->connect("DBI:mysql:database=zeus:host=$host", $user, $pwd, {'RaiseError' => 1}) or die "Connect to $host error:". DBI->errstr
    or die "Can't Connect to Information DB $host, Please Check!\n";
    # 根据系统类型设置清屏命令
    if ($^O eq "MSWin32") {
        $clear = "cls";
    } else {
        $clear = "clear";
    }
}

#######################################################
# Get Command
# 获取命令
#######################################################
sub get_cmd {
    my $local = ($_[0]);
    if (not defined($local)) {
        $local = 'none';
    }
    # 命令前显示正在操作的位置
    print "($local) >> ";
    my $cmd  = <>;
    chop($cmd);
    $cmd = lc($cmd);
    # 切分命令
    my @cmds = split(/ /, $cmd);
    @cmds;
}

#######################################################
# Get SQL
# 获取SQL
#######################################################
sub get_sql {
    my $local = ($_[0]);
    my $flag = 1;
    my $sql = '';

    printf "($local) Run >> ";
    while ($flag) {
        $sql .= <>;
	# 以分号为终结
        if ($sql =~ m/.*;$/) {
            #printf "$sql\n";
            $flag = 0;
        } else {
            for (my $i=0; $i<length($local)+7; $i++) {
                print " ";
            }
            print "-> ";
        }
    }
    $sql;
}

#######################################################
# Insert Data
# 插入信息
#######################################################
sub insert {
    my $local = ($_[0]);
    my $sql = '';

    if ($local eq $c_host) {
        my $name = '';
        my $ip = ''; 

        printf "($local) Insert: Name >> ";
        $name = <>;
        chomp($name);
        printf "($local) Insert: IP >> ";
        $ip = <>;
        chomp($ip);

        $sql = "insert into `hosts` (host_name, host_ip) values ('$name', '$ip')";
        #print $sql;
    } elsif ($local eq $c_instance) {
        my $type = 'mysql';   #Default mysql
        my $name = 'root';    #Default root
        my $psw = '';         #Default empty
        my $port = '3306';     #Default 3306
        my $host = 1;         #Default 1  
        my $tmp;

        printf "($local) Insert: Type(Default mysql) >> ";
        $tmp = <>;
        chomp($tmp);
        if (defined($tmp) && (not ($tmp eq ''))) {
            $type = $tmp;
        }
        printf "($local) Insert: Name(Default root) >> ";
        $tmp = <>;
        chomp($tmp);
        if (defined($tmp) && (not ($tmp eq ''))) {
            $name = $tmp;
        }
        printf "($local) Insert: Password(Default '') >> ";
        $tmp = <>;
        chomp($tmp);
        if (defined($tmp) && (not ($tmp eq ''))) {
            $psw = $tmp;
        }
        printf "($local) Insert: Port(Default 3306) >> ";
        $tmp = <>;
        chomp($tmp);
        if (defined($tmp) && (not ($tmp eq ''))) {
            $port = $tmp;
        }
        printf "($local) Insert: Host(Default 1) >> ";
        $tmp = <>;
        chomp($tmp);
        if (defined($tmp) && (not ($tmp eq ''))) {
            $host = $tmp;
        }

        $sql = "insert into `instances` (instance_type, instance_name, instance_password, instance_port, host_id) 
                values ('$type', '$name', '$psw', '$port', $host)";
        #print $sql;
    } elsif ($local eq $c_schema) {
        my $name = '';
        my $instance = 0; 

        printf "($local) Insert: Name >> ";
        $name = <>;
        chomp($name);
        printf "($local) Insert: Instance >> ";
        $instance = <>;
        chomp($instance);

        $sql = "insert into `schemas` (schema_name, instance_id) values ('$name', $instance)";
        #print $sql;
    } elsif ($local eq $c_cluster) {
        my $name = '';
        my $comment = ''; 
        my $target = '';

        printf "($local) Insert: Name >> ";
        $name = <>;
        chomp($name);
        printf "($local) Insert: Comment >> ";
        $comment = <>;
        chomp($comment);
        printf "($local) Insert: Target >> ";
        $target = <>;
        chomp($target);

        $sql  = "insert into `clusters` (cluster_name, cluster_comment) values ('$name', '$comment');";
        $dbh->do($sql);
        $sql = 'select last_insert_id() into @cluster_id;';
        $dbh->do($sql);

        my @targets = split(/,/, $target); 
        foreach my $t (@targets) {
            $sql = 'insert into `cluster_relations` (cluster_id, target_id) values (@cluster_id,'."$t);";
            $dbh->do($sql); 
        }
        return ;
        #print "\n",$sql,"\n";
    }
    $dbh->do($sql); 
}

#######################################################
# Modify Data
# 修改信息
#######################################################
sub modify {
}

#######################################################
# Delete Data
# 插入信息
#######################################################
sub delete {
}

#######################################################
# Main Menu
# 显示主菜单
#######################################################
sub main_menu {
    while (1) {
        system($clear);
        {
            printf <<EOF
=============== MySQL Cluster Manager $version ===============
Choose Task:
    1. Host Manager
    2. Instance Manager
    3. Schema Manager
    4. Cluster Manager
    5. Exit
EOF
         }
        # 分析命令
        my @cmds = get_cmd('none');
        my $cmd = $cmds[0];
        if ($cmd == 1) {
            &host_menu();
        } elsif ($cmd == 2) {
            &instance_menu();
        } elsif ($cmd == 3) {
            &schema_menu();
        } elsif ($cmd == 4) {
            &cluster_menu();
        } elsif ($cmd == 5) {
            print "Exiting......\n";
            $dbh->disconnect();
            exit 0;
        }
    }
}

#######################################################
# Host Menu
# 显示主机菜单
#######################################################
sub host_menu {
    while (1) {
        system($clear);
        {
            printf <<EOF
=============== MySQL Cluster Manager - Host Manager ===============
+ a -- Add Host         m -- Modify Host          d -- Delete Host +
+ v -- View Instance    u -- Up Dir               q -- Quit        +
+------------------------------------------------------------------+
|      ID      |        Host_Name        |         Host_IP         |
+--------------+-------------------------+-------------------------+
EOF
        }
        my $sql = "select id,host_name,host_ip from hosts";
        my $rows = $dbh->selectall_arrayref($sql); 
        foreach my $row(@$rows) {
            printf ("|%13s |%24s |%24s |\n", $row->[0], $row->[1], $row->[2]);
        }

        my @cmds = get_cmd($c_host);
        my $cmd = $cmds[0];

        if ($cmd eq "a") { 
            &insert($c_host);
        } elsif ($cmd eq "m") {

        } elsif ($cmd eq "d") {

        } elsif ($cmd eq "v") {
            my $id = $cmds[1];
            &instance_menu($id);
        } elsif ($cmd eq "u") {
            &main_menu();
        } elsif ($cmd eq "q") {
            print "Exiting......\n";
            $dbh->disconnect();
            exit 0;
        }
    }
}

#######################################################
# Instance Menu
# 显示实例菜单
#######################################################
sub instance_menu {
    my $host_id = ($_[0]);
    while (1) {
        system($clear);
        {
            printf <<EOF
============= MySQL Cluster Manager - Instance Manager =============
+ a -- Add Instance   m -- Modify Instance    d -- Delete Instance +
+ v -- View Schema    u -- Up Dir             q -- Quit            +
+------------------------------------------------------------------+
|   ID   | Inst_Type | Inst_Port |  Host_Name  |      Host_IP      |
+--------+-----------+-----------+-------------+-------------------+
EOF
        }
        my $sql = "SELECT i.id, instance_type, instance_port, host_name, host_ip
                   FROM hosts h, instances i 
                   WHERE i.host_id = h.id ";
        if (defined($host_id)) {
            $sql .= "AND h.id = $host_id";
        }
        my $rows = $dbh->selectall_arrayref($sql); 
        foreach my $row(@$rows) {
            printf ("|%7s |%10s |%10s |%12s |%18s |\n", $row->[0], $row->[1], $row->[2], $row->[3], $row->[4]);
        }

        my @cmds = get_cmd('Instances');
        my $cmd = $cmds[0];
        if ($cmd eq "a") {
            &insert($c_instance); 
        } elsif ($cmd eq "m") {
        } elsif ($cmd eq "d") {
        } elsif ($cmd eq "v") {
            my $id = $cmds[1];
            &schema_menu($id);
        } elsif ($cmd eq "u") {
            &main_menu();
        } elsif ($cmd eq "q") {
            print "Exiting......\n";
            $dbh->disconnect();
            exit 0;
        }
    }
}

#######################################################
# Schema Menu
# 显示模式菜单
#######################################################
sub schema_menu {
    my $instance_id = ($_[0]);
    while (1) {
        system($clear);
        {
            printf <<EOF
============= MySQL Cluster Manager - Schema Manager ===============
+ a -- Add Schema      m -- Modify Schema       d -- Delete Schema +
+ u -- Up Dir                                   q -- Quit          +
+------------------------------------------------------------------+
|   ID   |  Schm_Name  | Inst_Type | Host_Port |      Host_IP      |
+--------+-------------+-----------+-----------+-------------------+
EOF
        }
        my $sql = "SELECT s.id, schema_name, instance_type, instance_port, host_ip
                   FROM `hosts` h, `instances` i, `schemas` s 
                   WHERE i.host_id = h.id 
                   AND s.instance_id = i.id ";
        if (defined($instance_id)) {
            $sql .= "AND i.id = $instance_id";
        }
        my $rows = $dbh->selectall_arrayref($sql); 
        foreach my $row(@$rows) {
            printf ("|%7s |%12s |%10s |%10s |%18s |\n", $row->[0], $row->[1], $row->[2], $row->[3], $row->[4]);
        }

        my @cmds = get_cmd('Schemas');
        my $cmd = $cmds[0];
 
        if ($cmd eq "a") { 
            &insert($c_schema);
        } elsif ($cmd eq "m") {
        } elsif ($cmd eq "d") {
        } elsif ($cmd eq "u") {
            &main_menu();
        } elsif ($cmd eq "q") {
            print "Exiting......\n";
            $dbh->disconnect();
            exit 0;
        }
    }
}


#######################################################
# Cluster Menu
# 显示集群菜单
#######################################################
sub cluster_menu {
    my $cluster_name = ($_[0]);
    while (1) {
        my $cmd_comment = 'Cluster';
        system($clear);
        {
            printf <<EOF
============= MySQL Cluster Manager - Cluster Manager ==============
+ a -- Add Cluster     m -- Modify Cluster     d -- Delete Cluster +
+ s -- Select Cluster  u -- Up Dir             q -- Quit           +
+ r -- Run SQL                                                     +
+------------------------------------------------------------------+
|   ID   | Clst_Name | Trg_Type |   Trg_Name   |      Host_IP      |
+--------+-----------+----------+--------------+-------------------+
EOF
        }
        my $sql1 = "SELECT c.id, cluster_name, target_type, target_id
                    FROM clusters c, cluster_relations cr
                    WHERE c.id = cr.cluster_id ";
        if (defined($cluster_name)) {
            $sql1 .= "AND c.cluster_name = '$cluster_name'";
            $cmd_comment .= ": $cluster_name";
        }
        my $rows1 = $dbh->selectall_arrayref($sql1); 
        foreach my $row1(@$rows1) {
            printf ("|%7s |%10s |%10s |", $row1->[0], $row1->[1], $row1->[2]);

            my ($sql2, $rows2);
            if ($row1->[2] eq 'instance') {
                $sql2 = "SELECT instance_name, host_ip
                         FROM instances i, hosts h
                         WHERE i.id = $row1->[3]
                          AND i.host_id = h.id;";
                $rows2 = $dbh->selectall_arrayref($sql2); 
            } elsif ($row1->[2] eq 'schema') {
                $sql2 = "SELECT schema_name, host_ip
                         FROM `schemas` s, instances i, hosts h
                         WHERE s.id = $row1->[3]
                         AND s.instance_id = i.id
                         AND i.host_id = h.id;";
                $rows2 = $dbh->selectall_arrayref($sql2); 
            }
            foreach my $row2(@$rows2) {
                printf ("%12s |%18s |\n", $row2->[0], $row2->[1]);
            }
        }

        my @cmds = get_cmd($cmd_comment);
        my $cmd = $cmds[0];

        if ($cmd eq "a") { 
            &insert($c_cluster);
        } elsif ($cmd eq "m") {
        } elsif ($cmd eq "d") {
        } elsif ($cmd eq "s") {
           &cluster_menu($cmds[1]);
        } elsif ($cmd eq "r") {
           if (defined($cluster_name)) {
               my $sql = "SELECT id 
                          FROM clusters c
                          WHERE c.cluster_name = '$cluster_name'";
               my $cluster_id = $dbh->selectrow_array($sql);
               &create_conn($cluster_id);
               $sql = get_sql($cmd_comment);
               &run($sql);
           } else {
               printf "No Select Cluster";
               <>;
           } 
        } elsif ($cmd eq "u") {
            &main_menu();
        } elsif ($cmd eq "q") {
            print "Exiting......\n";
            $dbh->disconnect();
            exit 0;
        } else {}
    }
}

#######################################################
# Create MySQL Database Connect 
# 创建MySQL数据库连接
#######################################################
sub create_conn {
    my $cluster_id = ($_[0]);
    my $i = 0;
    
    my $sql = "SELECT host_ip, instance_name, instance_password, instance_port, schema_name
               FROM `hosts` h, `instances` i, `schemas` s, `cluster_relations` cr
               WHERE h.id = i.host_id
               AND i.id = s.instance_id
               AND s.id = cr.target_id
               AND cr.cluster_id = $cluster_id";
    my $rows = $dbh->selectall_arrayref($sql); 

    foreach my $row (@$rows){
        my $host = $row->[0];
	my $user = $row->[1];
	my $pwd = $row->[2];
        my $port = $row->[3];
        my $db = $row->[4];
	my $sid;
	    #if($db_type eq 'oracle') {
	    #    $sid = $srv[3];
            #    $port = $srv[4];
	    #}
        #print "$host.$user.$pwd\n";
        eval{
            local $SIG{ALRM} = sub { die "连接数据库超时\n" };
            alarm 20;
            $dbconn[$i] = DBI->connect("DBI:mysql:database=$db:host=$host:port=$port", $user, $pwd, {'RaiseError' => 1, 'AutoCommit' => 0}) 
            or die "Connect to $host error:". DBI->errstr;
	    #if ($db_type eq 'oracle') {
            #        $dbconn[$i] = DBI->connect("DBI:Oracle:host=$host;sid=$sid;port=$port", $user, $pwd, {'RaiseError' => 1}) 
            #        or die "Connect to $host error:". DBI->errstr;
	    #}
            alarm 0;
        };   
        if($@){
            printf "Connect to $host database error:".$@."\n";
            exit;
        }
        $i++;
    }
}

#######################################################
# Main Program to Run SQL on Server List
# 在服务器列表上执行一组SQL
#######################################################
sub run {
    my $sql = ($_[0]);
    foreach my $conn (@dbconn) {
        $conn->do($sql) || die DBI->errstr; 
        $conn->commit(); 
    }
}

#######################################################
# Disconnect from MySQL
# 从数据库断开连接
#######################################################
sub close_conn(){
    foreach my $conn (@dbconn) {
        $conn->disconnect;
    }
}
